<html>
	<body>
	<%@ page import="java.sql.*,studentportal.*"%>

<%
			Connection con1=MySQLDriver.getConnection(); 
			con1.setAutoCommit(false);
			Statement st1=con1.createStatement();
			String query="select count(*) from book_borrow where status!='R' and book_name='"+request.getParameter("name")+"' and author='"+request.getParameter("author")+"' and edition='"+request.getParameter("edition")+"' and student_id="+session.getAttribute("StudentId");
			ResultSet rs=st1.executeQuery(query);
			int count=0;
			if(rs.next())
			 count=rs.getInt(1);
			rs.close();
			st1.close(); 
			if(count==0) {
			st1=con1.createStatement();
			query="select book_id from book_master where book_name='"+request.getParameter("name")+"' and author='"+request.getParameter("author")+"' and edition='"+request.getParameter("edition")+"' and book_status='N' and  book_id not in (select book_id from book_borrow where status='A')";
			rs=st1.executeQuery(query);
			String bookId=null;
			if(rs.next()) {
			  bookId=rs.getString(1);
			}
			rs.close();
			st1.close();
			System.out.println("ssssssssssssssssssssss="+session.getAttribute("StudentId"));
			if(bookId==null) {
		

  %>
		
    <p align="center">&nbsp;</p>
    <p align="center">&nbsp;</p>
    <p align="center"><font size="6" color="#0066FF">Requested Book Is Not Avaliable.</font></p>
    <%} else{ 
    
    
	  query="insert into book_borrow(book_id, student_id, request_date,book_name, author, edition) values(?,?,?,?,?,?)";
      PreparedStatement st2=con1.prepareStatement(query);
      st2.setString(1,bookId);
      st2.setString(2,(String)session.getAttribute("StudentId"));
      st2.setTimestamp(3,new Timestamp(System.currentTimeMillis()));
      st2.setString(4,(String)request.getParameter("name"));
      st2.setString(5,(String)request.getParameter("author"));
      st2.setString(6,(String)request.getParameter("edition"));
      st2.executeUpdate();
      st2.close();
      con1.commit();
    %>
    <p align="center">&nbsp;</p>
    <p align="center">&nbsp;</p>
    <p align="center"><font size="6" color="#0066FF">Requested Book Is Avaliable.</font></p>
    
    <%}}else { %>
    <p align="center">&nbsp;</p>
    <p align="center">&nbsp;</p>
    <p align="center"><font size="6" color="#0066FF">Already Book Is Requested.</font></p>
    
    <%} %>
    <p align="center"><a href="Student_List_Book.jsp">Back</a></p>
	</body>
</html>
